package com.zha;

import com.etc.User;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

public class ReadExcel {
    public static void main(String[] args) throws IOException, SQLException {
        String fileName = "f:/all/student.xlsx";
        FileInputStream fis = new FileInputStream(fileName);
        Workbook workbook = null;
        //判断Excel的两种格式xls,xlsx
        if (fileName.toLowerCase().endsWith("xlsx")){
            workbook = new XSSFWorkbook(fis);
        }
        //得到sheet的总数
        int numberOfSheets = workbook.getNumberOfSheets();
        ArrayList<User> userList = new ArrayList<User>();
        //循环每一个sheet
        for (int i = 0; i < numberOfSheets; i++) {
            //得到第i个sheet
            Sheet sheet = workbook.getSheetAt(i);
            //得到行的迭代器
            Iterator<Row> rowIterator = sheet.iterator();
            //循环每一行
            while (rowIterator.hasNext()){
                User user = new User();
                //得到一行对象
                Row row = rowIterator.next();
                int rowNum = row.getRowNum();
                if (rowNum == 0){
                    continue;
                }
                //得到列对象
                Iterator<Cell> cellIterator = row.cellIterator();
                int number = 0;
                while ( cellIterator.hasNext() ){
                    Cell next = cellIterator.next();
                    if (number == 0){
                        double numericCellValue = next.getNumericCellValue();
                        user.setId(String.valueOf(numericCellValue));
                        number++;
                        continue;
                    }
                    if (number == 1){
                        String name = next.getStringCellValue();
                        user.setName(name);
                        number++;
                        continue;
                    }
                    if (number == 2){
                        String sex = next.getStringCellValue();
                        user.setSex(sex);
                        number++;
                        continue;
                    }
                    if (number == 3){
                        double num = next.getNumericCellValue();
                        user.setNum(String.valueOf(num));
                        number++;
                        continue;
                    }
                }
                add(user);
            }
            System.out.println();
        }

        fis.close();
    }

    public static void add(User user) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "insert into t_student value(?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        String id = user.getId();
        double v = Double.parseDouble(id);
        int a = (int)v;
        statement.setInt(1,a);
        statement.setString(2,user.getName());
        statement.setString(3,user.getSex());
        statement.setInt(4,(int)Double.parseDouble(user.getNum()));
        int i = statement.executeUpdate();
        System.out.println(i == 1 ? "success":"fail");
    }
    /**
     * 现在本地创建一个表然后上传
     * id	name	sex	num
     * 1	小明	male	100
     * 2	张三	female	200
     * 3	李四	male	300
     * 4	王五	female	400
     */
}


